/**
 * This Class is used to perform the interaction with the database.
 * This class will be called only from the server to execute queries and to return results from the database.
 * 
 * @author Gil Levinson
 * @author Boris Fradlis
 * @author Yogev Levi
 * @author Hila Porat
 *  
 */

package system;
import org.joda.time.DateTime;
import org.joda.time.Days;
import org.joda.time.Hours;
import org.joda.time.Minutes;
import org.joda.time.Seconds;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import vcpClasses.*;

import java.io.Serializable;
import java.sql.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.Timer;
import java.util.TimerTask;
import java.util.Vector;


//Start of DataBaseConnection

public class DataBaseConnection  {

	// Class variables ***********
	
	private Connection conn;
	private final  String host = "localhost";
	private final  String lib = "vcp";
	private	final String user = "root";
	private String password = "";
	private Statement stmt;
	private int lastnid = 0, lastrid = 0, lastcid = 0, lastrepid = 0, lastremid = 0, lastsid = 0, lastwid = 0;
	
	
	/**
	 * Empty Constructor for Junit
	 */
	public DataBaseConnection(){}
	/**
	 * Construct an instance of the driver to the database and also saves the password received from the user.
	 * Upon completion of this construct, server will be able to open the connection to the user when it needs to.  
	 */
	
	
	public DataBaseConnection(String pass){
		this.getDataBaseConnectionDetails(pass);
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			InitiateServer.serverStatus = new String("Running");
		}
		catch  (Exception ex) {System.out.println("no driver !!!"); System.exit(0);}
		
		
	}
	
	/**
	 * Open the connection to the database when asked by server and gives notification to the server if succeed.
	 */
		
	public void openConnectionToDataBase(){
	
		try 
	    {
	        setConn(DriverManager.getConnection("jdbc:mysql://" + host +"/"+lib,user,password));
	        
	        System.out.println("SQL connection succeed");
	    }
		catch (SQLException ex)  {/* handle any errors*/
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
	}
	
	/**
	 * Close the connection to the database when asked by server.
	 */
	
	public void closeConnectionToDataBase(){
		try {
			this.conn.close();
			InitiateServer.serverStatus = "Not Running";
		} catch (SQLException e) {
			System.out.println("Unable to close DB connection\n");
		}
	}
	
	/**
	 * Ask for the connection password and set it.
	 */
	
	public void getDataBaseConnectionDetails(String pass){
		setPassword(pass);		
	}
	
	/**
	 * This method will check if userName exist in table and compare its password
	 * @param t - the object contains the user  
	 * @return instance of User with the status of the result of the query
	 * DB_CON_ERR - if there was some DB Failure
	 * OK - if everything OK
	 * FAIL - wrong password
	 * EMPTY_RES - no such User
	 */
	
	@SuppressWarnings("null")
	public SystemTypes verifyUserDetails(SystemTypes t,String ip) {
		ResultSet rs;
		Serializable obj[] = new Serializable[2];
		User compare = new User((User) t.getObj());
		ParkingLot lot = new ParkingLot(ip);
		int lotNum = 1;
				
		try 
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM user WHERE UserName = '" + compare.getUserName() + "';");
			if (rs.first() == false)
			{
				t.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("Login failed - no such user.");
				obj[0] = compare;
				obj[1] = lot;
				t.setObj(obj);
				return t;
			}
			String pass = rs.getString("userPassword");
			int priv = rs.getInt("privilege");
			int logged = rs.getInt("loggedIn");
			if (pass.equals(compare.getUserPassword()))
			{
					if (logged == 0)
					{
						t.setSysMode(SystemMode.OK);
						System.out.println("Login succeded.");
						compare.setPrivilege(priv);
						compare.setUserEmailAddress(rs.getString("userEmailAddress"));
						compare.setUserFirstName(rs.getString("userFirstName"));
						compare.setUserLastName(rs.getString("userLastName"));
						
						//=============== Define user type according to privilege ==============//
						switch (priv)
						{
						case 1:	//Network Manager
						case 2:	//Parking Lot Manager
						case 3:	//Parking Lot Worker
						case 4: //Customer Service Worker
							Worker newWorker = new Worker(compare);
							rs = stmt.executeQuery("SELECT * FROM worker WHERE UserName = '"+newWorker.getUserName()+"';" );
							if (rs.next())
							{
								newWorker.setWorkerNumber(rs.getInt("workerNumber"));
								newWorker.setWorkerRole(rs.getString("workerRole"));
								newWorker.setWorkerAssociation(rs.getInt("workerAssociation"));
								lotNum = newWorker.getWorkerAssociation();
								compare = newWorker;
								rs.close();
							}
							break;
						case 5: //Client
							Client newClient = new Client(compare);
							rs = stmt.executeQuery("SELECT * FROM client WHERE UserName = '"+newClient.getUserName()+"';" );
							if (rs.next())
							{
								newClient.setClientBalance(rs.getFloat("clientBalance"));
								newClient.setClientNumber(rs.getInt("clientNumber"));
								newClient.setClientPhone(rs.getString("clientPhone"));
								newClient.setNumberOfVehicles(rs.getInt("numberOfVehicles"));
								compare = newClient;
								/*rs = stmt.executeQuery("SELECT * FROM subscribedclient WHERE clientNumber = '"+newClient.getClientNumber()+"';" );
								if (rs.next())
								{
									SubscribedClient newSubClient = new SubscribedClient();
									newSubClient.setSubscriptionNumber(rs.getInt("subscriptionNumber"));
									newSubClient.setStartDate(rs.getString("startDate"));
									newSubClient.setClientLeaveTime(rs.getString("clientLeaveTime"));
									newSubClient.setTypeOfSubscription(rs.getString("typeOfSubscription"));
									newSubClient.setVehicleNumber(rs.getInt("vehicleNumber"));
									newSubClient.setLotNumber(rs.getInt("lotNumber"));
									lotNum = newSubClient.getLotNumber();
									compare = newSubClient;
								}
								rs.close();*/
							}
							break;
						}
						//====================================================================//
						rs = stmt.executeQuery("SELECT * FROM parkinglot WHERE kioskIP = '"+ip+"."+lotNum+"';" );
						if (rs.first() == false)
								lot.setParkingLotNumber(0);
						else
						{
							
							lot.setParkingLotNumber(rs.getInt("parkingLotNumber"));
							lot.setLotStatus(rs.getString("lotStatus"));
							lot.setNumOfSpacesInRow(rs.getInt("spaceNumber"));
							lot.setAdvanceRate(rs.getFloat("advanceRate"));
							lot.setOccasionalRate(rs.getFloat("occasionalRate"));
							lot.setHoursForNormal(rs.getFloat("hoursForNormal"));
							lot.setHoursForRoutineS(rs.getFloat("hoursForRoutineS"));
							lot.setHoursForRoutineM(rs.getFloat("hoursForRoutineM"));
							
						}
					}
					else 
					{
						t.setSysMode(SystemMode.ALREADY_IN);
						System.out.println("Login failed - user currently logged in.");
						compare.setPrivilege(rs.getInt("loggedIn"));
					}
					stmt.executeUpdate("UPDATE user SET loggedIn = 1 WHERE UserName = '"+ compare.getUserName() +"';" );
					
			}
			else
			{
				t.setSysMode(SystemMode.FAIL);
				System.out.println("Login failed - Incorrect password.");
			}
		} 
		catch (SQLException e) 
		{
			System.out.println("Unable to update the query.");
			t.setSysMode(SystemMode.DB_CON_ERR);			
		}
		
		obj[0] = compare;
		obj[1] = lot;
		t.setObj(obj);
		return t;
	}
	
	/**
	 * this method will logOut the currentUser from the DB
	 * @param sysType - the user to logout
	 * @return - the proper message for the result
	 */

	public SystemTypes LogOut(SystemTypes sysType)
	{
		ResultSet rs;
		User user = (User) sysType.getObj();
		try
		{
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE user SET loggedIn = 0 WHERE UserName = '"+ user.getUserName() +"';");
		}
		catch (Exception e)
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			sysType.setObj("Cant Handle the Request against the DB!!!");
			return sysType;
		}
		sysType.setSysMode(SystemMode.OK);
		System.out.println("Logout Succeded.");
		return sysType;
	}
	
	/**
	 * this method will help us set different status to different devices
	 * @param toUpdate - the status to update!!
	 * @param sysType - the device to set status to
	 * @return - OK if succeed DB_CON_ERR if DB failed to work
	 */
	
	public SystemTypes setSpecialStatus(String toUpdate,SystemTypes sysType)
	{
		if (sysType.getObj() instanceof ParkingLocation)
		{
			ParkingLocation location = new ParkingLocation (sysType.getObj());
			try 
			{
				stmt = conn.createStatement();
				stmt.executeUpdate("UPDATE parkingLocation SET parkingSpaceStatus = '" + toUpdate +"' WHERE (lotNumber = "+location.getParkingLotNumber()+") AND (floorNumber = " + location.getParkingFloorNumber()
						+ ") AND (rowNumber = " + location.getParkingRowNumber() + ") AND (spaceNumber = " + location.getParkingSpaceNumber() +");");
				sysType.setObj("Succesfully operated the parking space!");
			}
			catch (SQLException ex) 
			{
				sysType.setSysMode(SystemMode.DB_CON_ERR);
				sysType.setObj("Can't operate the space against the DB!");
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			sysType.setSysMode(SystemMode.OK);
			System.out.println("Status updated Succesfully.");
		}
		if (sysType.getObj() instanceof ParkingRow)
		{
			ParkingRow row = new ParkingRow (sysType.getObj());
			try 
			{
				stmt = conn.createStatement();
				stmt.executeUpdate("UPDATE parkingLocation SET parkingSpaceStatus = '" + toUpdate +"' WHERE (lotNumber = "+row.getLotNumber()+") AND (floorNumber = " + row.getFloorNumber()
						+ ") AND (rowNumber = " + row.getRowNumber() + ");");
				sysType.setObj("Succesfully operated the parking row!");
			}
			catch (SQLException ex) 
			{
				sysType.setSysMode(SystemMode.DB_CON_ERR);
				sysType.setObj("Can't operate the row against the DB!");
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			sysType.setSysMode(SystemMode.OK);	
			System.out.println("Status updated Succesfully.");
		}
		if (sysType.getObj() instanceof ParkingFloor)
		{
			ParkingFloor floor = new ParkingFloor (sysType.getObj());
			try 
			{
				stmt = conn.createStatement();
				stmt.executeUpdate("UPDATE parkingLocation SET parkingSpaceStatus = '" + toUpdate +"' WHERE (lotNumber = "+floor.getLotNumber()+") AND (floorNumber = " + floor.getFloorNumber()+ ");");
				sysType.setObj("Succesfully operated the parking floor!");
				
			}
			catch (SQLException ex) 
			{
				sysType.setSysMode(SystemMode.DB_CON_ERR);
				sysType.setObj("Can't operate the floor against the DB!");
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			sysType.setSysMode(SystemMode.OK);	
			System.out.println("Status updated Succesfully.");
		}
		if (sysType.getObj() instanceof ParkingLot)
		{
			ParkingLot lot = new ParkingLot ((ParkingLot)sysType.getObj());
			try 
			{
				stmt = conn.createStatement();
				stmt.executeUpdate("UPDATE parkingLocation SET parkingSpaceStatus = '" + toUpdate +"' WHERE (lotNumber = "+lot.getParkingLotNumber()+");");
				sysType.setObj("Succesfully operated the parking lot!");
			}
			catch (SQLException ex) 
			{
				sysType.setSysMode(SystemMode.DB_CON_ERR);
				sysType.setObj("Can't operate the lot against the DB!");
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			sysType.setSysMode(SystemMode.OK);
			System.out.println("Status updated Succesfully.");
		}
		return sysType;
	} // End Of setSpecialStatus
	
	/**
	 * this method will set the current parking lot as full a will return an available parking lot  
	 * @param sysType - the lot to define as full
	 * @return - a free parking lot if any
	 */

	public SystemTypes SetFullLot(SystemTypes sysType)
	{
		Serializable obj[] = new Serializable[2];
		obj = (Serializable[]) sysType.getObj();
		String str = (String) obj[0];
		int n;
		ResultSet rs;
		ParkingLot lot;
		int lotNumber = ((ParkingLot) (obj[1])).getParkingLotNumber();
		try
		{
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE parkinglot SET lotStatus = '"+str+"' WHERE parkingLotNumber = "+lotNumber+";");
			sysType.setSysMode(SystemMode.OK);
			System.out.println("Status updated Succesfully.");
			if (str.equals("Full"))
				sysType = this.findFreeLot();
		}
		catch (SQLException ex)
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		return sysType;
	} // end of SetFullLot

	/**
	 * this method will return the first free lot
	 * @return free lot
	 */
	
	public SystemTypes findFreeLot()
	{
		SystemTypes sysType = new SystemTypes();
		ResultSet rs;
		ParkingLot lot;
		try
		{
			
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM parkinglot WHERE lotStatus = 'Not Full';");
			if (rs.first() == false)
			{
				sysType.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("No alternative lot found. All lots are full");
				sysType.setObj(null);
				return sysType;
			}
			else
				{
					String str = rs.getString("kioskIP");
					int	num = rs.getInt("parkingLotNumber");
					System.out.println("Found alternative lot no: "+num+".");
					lot = new ParkingLot(num,str);
				}
			sysType.setSysMode(SystemMode.OK);
			sysType.setObj(lot);
		}
		catch (SQLException e)
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return sysType;
	}
	
	/**
	 * this method will initialize the lot in the start of system
	 * @param sysType - the lot to initialize
	 * @return - the string representing the end of the query.
	 */
	
	public SystemTypes lotInit(SystemTypes sysType)
	{
		int i,j,k,spaces = 0,lotNum, sid = 1;
		ResultSet rs;
		ParkingLot lot = (ParkingLot) sysType.getObj();
		lotNum = lot.getParkingLotNumber();
		spaces = lot.getNumOfSpacesInRow();
		try
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM parkingLocation WHERE lotNumber = "+lotNum+";");
			if (rs.first())
			{
				sysType.setSysMode(SystemMode.FAIL);
				System.out.println("Initialize failed. Worker Initialize the lot earlier.");
				return sysType;
			}
			else
			{
				rs = stmt.executeQuery("SELECT * FROM parkingLocation;");
				if (rs.first())
				{
					rs.last();
					sid = rs.getInt("sid") + 1;
				}
				for (i=1;i<4;i++)
					for(j=1;j<4;j++)
						for (k = 1; k <= spaces ; k++ )
						{
							stmt.executeUpdate("INSERT INTO `vcp`.`parkinglocation` (`sid`, `lotNumber`, `floorNumber`, `rowNumber`, `spaceNumber`, `parkingSpaceStatus`) VALUES ('"+sid+"', '"+lotNum+"', '"+i+"', '"+j+"', '"+k+"', 'FREE');");
							sid++;
						}
				sysType.setSysMode(SystemMode.OK);
				System.out.println("Initialize succeded.");
			}
		}
		catch (SQLException e)
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return sysType;
	} // end of lotInit
	
	/**
	 * this method will send to the controller the relevant dates for an order, to calculate the actual parking time.   
	 * @param sysType - the relevant order 
	 * @return the dates if all conditions met, and error otherwise
	 */
	
	public SystemTypes getActualTime(SystemTypes sysType){
		Order order = (Order) sysType.getObj();
		ResultSet rs;
		try
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM order WHERE orderNumber = "+order.getOrderNumber()+";");
			if (rs.first() == false)
			{
				sysType.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("Calculation failed - Incorrect order number.");
				return sysType;
			}
			String status = rs.getString("orderStatus");
			if (!status.equals("Closed"))
			{
				sysType.setSysMode(SystemMode.FAIL);
				System.out.println("Calculation failed - order not closed yet.");
				return sysType;
			}
			Date date[] = new Date[2];
			date[0] = rs.getDate("orderDate");
			date[1].setTime(System.currentTimeMillis());
			sysType.setObj(date);
			sysType.setSysMode(SystemMode.OK);
			System.out.println("Calculations succeded.");
		}
		catch  (SQLException e)
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return sysType;
	}
	
	
	/**
	 * this method can be used to get the snapshot of the lot at the present point
	 * @param sysType - the lot that we enquire 
	 * @return the vector of all parking spaces in the lot
	 */
	
	public SystemTypes getParkingLocationSnapshot(SystemTypes sysType)
	{
		ParkingLot lot =(ParkingLot) sysType.getObj();
		ParkingLocation location = new ParkingLocation();
		Vector<ParkingLocation> vec = new Vector<ParkingLocation>();
		ResultSet rs;
		try 
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM parkinglocation WHERE lotNumber = "+lot.getParkingLotNumber()+";");
			if (rs.first() == false)
			{
				sysType.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("Fetching lot details failed- Incorrect lot number.");
				return sysType;
			}
			do 
			{
				location = new ParkingLocation();
				location.setParkingLotNumber(lot.getParkingLotNumber());
				location.setParkingFloorNumber(rs.getInt("floorNumber"));
				location.setParkingRowNumber(rs.getInt("rowNumber"));
				location.setParkingSpaceNumber(rs.getInt("spaceNumber"));
				location.setParkingSpaceStatus(rs.getString("parkingSpaceStatus"));
				vec.add(location);
			}
			while (rs.next());
			
			sysType.setObj(vec);
			sysType.setSysMode(SystemMode.OK);
			System.out.println("Fetching lot details succeded.");
		
		}
		catch (SQLException e) 
		{
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println(sysType.getSysMode().toString());
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			
		}
		return sysType;
	}

	/**
	 * this method will pull all data from needed table for the controller of read-only data
	 * @param sysType - the type of data to pull
	 * @return - the vector of object represented by rows of the proper table
	 */
	
	public SystemTypes getReadOnlyData(SystemTypes sysType)
	{
		String type = (String) sysType.getObj();
		Order order = new Order();
		Worker worker = new Worker();
		Client client = new Client();
		SubscribedClient sClient = new SubscribedClient();
		Vehicle vehicle = new Vehicle();
		ParkingLot lot = new ParkingLot();
		Report report = new Report();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm");
		ResultSet rs;
		try 
		{
			stmt = conn.createStatement();
			switch (type)
			{
				case "Orders":
				{
					Vector<Order> v = new Vector<Order>();
					rs = stmt.executeQuery("SELECT * FROM order;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching orders details failed - no orders found.");
						return sysType;
					}
					rs.beforeFirst();
					while (rs.next())
					{
						order = new Order();
						order.setOrderNumber(rs.getInt("orderNumber"));
						order.setOrderDate(thedate.format(rs.getDate("orderDate")));
						order.setOrderTime(thetime.format(rs.getTime("orderTime")));
						order.setArrivalTime(thedate.format(rs.getDate(4)));
						order.setOrderType(rs.getString("orderType"));
						order.setExpectedCost(rs.getFloat("expectedCost"));
						order.setActualCost(rs.getFloat("actualCost"));
						order.setExpectedLeaveTime(thedate.format(rs.getTime("expectedLeaveTime")));
						order.setOrderStatus(rs.getString("orderStatus"));
						order.setClientNumber(rs.getInt("clientNumber"));
						order.setVehicleNumber(rs.getInt("vehicleNumber"));
						order.setSidParkingLocation(rs.getInt("sidParkingLocation"));
						order.seteMail(rs.getString("eMail"));
						v.add(order);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Reports":
				{
					Vector<Report> v = new Vector<Report>();
					rs = stmt.executeQuery("SELECT * FROM reports;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching orders details failed - no orders found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						report.setRid(rs.getInt("rid"));
						report.setType(rs.getString("rtype"));
						report.setDate(rs.getString("rdate"));
						report.setTime(rs.getString("rtime"));
						v.add(report);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Workers":
				{
					Vector<Worker> v = new Vector<Worker>();
					rs = stmt.executeQuery("SELECT * FROM worker;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching workers details failed - no workers found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						worker.setWorkerNumber(rs.getInt("workerNumber"));
						worker.setWorkerRole(rs.getString("workerRole"));
						worker.setWorkerAssociation(rs.getInt("workerAssociation"));
						worker.setUserName(rs.getString("UserName"));
						v.add(worker);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Clients":
				{
					Vector<Client> v = new Vector<Client>();
					rs = stmt.executeQuery("SELECT * FROM client;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching client details failed - no clients found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						client = new Client();
						client.setClientNumber(rs.getInt("clientNumber"));
						client.setClientPhone(rs.getString(2));
						client.setClientBalance(rs.getFloat(4));
						client.setNumberOfVehicles(rs.getInt(3));
						client.setUserName((rs.getString(5)));
						v.add(client);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Subscribed Clients":
				{
					Vector<SubscribedClient> v = new  Vector<SubscribedClient>();
					rs = stmt.executeQuery("SELECT * FROM subscribedclient;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching subscriptions details failed - no subscriptions found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						sClient = new SubscribedClient();
						sClient.setSubscriptionNumber(rs.getInt(1));
						sClient.setStartDate(rs.getString(2));
						sClient.setClientLeaveTime(rs.getString(3));
						sClient.setTypeOfSubscription(rs.getString(4));
						sClient.setClientNumber(rs.getInt(5));
						sClient.setVehicleNumber(rs.getInt(6));
						sClient.setSubscriptionStatus(rs.getString(10));						
						v.add(sClient);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Vehicles":
				{
					Vector<Vehicle> v = new Vector<Vehicle>();
					rs = stmt.executeQuery("SELECT * FROM vehicle;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching vehicles details failed - no vehicles found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						vehicle = new Vehicle();
						vehicle.setVehicleNumber(rs.getInt(1));
						vehicle.setClientNumber(rs.getInt(2));									
						v.add(vehicle);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
				case "Parking lots":
				{
					Vector<ParkingLot> v = new Vector<ParkingLot>();
					rs = stmt.executeQuery("SELECT * FROM parkinglot;");
					if (rs.first() == false)
					{
						sysType.setSysMode(SystemMode.EMPTY_RES);
						System.out.println("Fetching Parking lots details failed - no Parking lots found.");
						return sysType;
					}
					rs.beforeFirst();
					while(rs.next())
					{
						lot = new ParkingLot();
						lot.setParkingLotNumber(rs.getInt(1));
						lot.setLotStatus(rs.getString(2));
						lot.setNumOfSpacesInRow(rs.getInt(3));
						lot.setAdvanceRate(rs.getFloat(4));
						lot.setOccasionalRate(rs.getFloat(5));
						lot.setHoursForNormal(rs.getFloat(6));
						lot.setHoursForRoutineS(rs.getFloat(7));
						lot.setHoursForRoutineM(rs.getFloat(8));
						lot.setKioskIP(rs.getString(9));
						v.add(lot);
					}
					sysType.setSysMode(SystemMode.OK);
					System.out.println("Fetching Information succeeded.");
					sysType.setObj(v);
					break;
				}
			}
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
			sysType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return sysType;
	}

	/**
	 * this method will send a rate change confirmation request to the network manager.
	 * @param sysType - the rate change confirmation request with the new rates.
	 * @return - a confirmation that the request was sent to the network manager.
	 */
	
	public SystemTypes SetRates(SystemTypes myType) {
		ResultSet rs;
		RateSettingRequest receivedNotification = (RateSettingRequest)myType.getObj();
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			if (receivedNotification.getType().equals("RATES"))
			{
				rs = stmt.executeQuery("SELECT * FROM ratechanges;");
				while (rs.next())
					lastrid = rs.getInt("rid");
				try {
					stmt = conn.createStatement();
					rs = stmt.executeQuery("SELECT * FROM worker WHERE workerRole = 'NET_MANAGER';");
					if (rs.next())
					{
						receivedNotification.setReceiver(rs.getString("UserName"));
						stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+receivedNotification.getType()+"', '"+receivedNotification.getContent().toString()+"', '"+receivedNotification.getSender()+"', '"+receivedNotification.getReceiver()+"', '0');");
						stmt.executeUpdate("INSERT INTO `vcp`.`ratechanges` (`rid`, `nid`, `newoccrate`, `newadvrate`, `newnormhours`, `newfullhours`)" +
								"VALUES ('"+(++lastrid)+"', '"+lastnid+"', '"+receivedNotification.getRatesToSet().getOccasional()+"', '"+receivedNotification.getRatesToSet().getAdvance()+"', '"+receivedNotification.getRatesToSet().getNormalSubHours()+"', '"+receivedNotification.getRatesToSet().getFullSubHours()+"');");
					}
					myType.setSysMode(SystemMode.OK);
					System.out.println("Requset sent succesfully.");
				}
				catch (SQLException e) {
					e.printStackTrace();
					myType.setSysMode(SystemMode.DB_CON_ERR);
					System.out.println("SQLException: " + e.getMessage());
					System.out.println("SQLState: " + e.getSQLState());
					System.out.println("VendorError: " + e.getErrorCode());
				}
				
			}
		}
		catch (SQLException e) {
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}
	
	/**
	 * this method will actually change the rates of a parking lot.
	 * @param sysType - the network manager's rate change approval notification.
	 * @return - a confirmation that the rates were changed.
	 */
	
	public SystemTypes ChangeRates(SystemTypes myType) {
		ResultSet rs;
		RateSettingRequest handledRequest = (RateSettingRequest)myType.getObj();
		String sender = handledRequest.getSender();
		Rates receivedRates = handledRequest.getRatesToSet();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			rs = stmt.executeQuery("SELECT * FROM worker WHERE UserName = '"+sender+"';");
			if (rs.next())
			{
				int workerAssociation = rs.getInt("workerAssociation");
				System.out.println(handledRequest.getType());
				if (handledRequest.getType().equals("APPROVED_RATES")){
					stmt.executeUpdate("UPDATE parkinglot SET advanceRate = '" + receivedRates.getAdvance() +"' WHERE (parkingLotNumber = "+workerAssociation+");");
					stmt.executeUpdate("UPDATE parkinglot SET occasionalRate = '" + receivedRates.getOccasional() +"' WHERE (parkingLotNumber = "+workerAssociation+");");
					stmt.executeUpdate("UPDATE parkinglot SET hoursForRoutineS = '" + receivedRates.getNormalSubHours() +"' WHERE (parkingLotNumber = "+workerAssociation+");");
					stmt.executeUpdate("UPDATE parkinglot SET hoursForRoutineM = '" + receivedRates.getNormalSubHours() +"' WHERE (parkingLotNumber = "+workerAssociation+");");
					stmt.executeUpdate("UPDATE parkinglot SET hoursForNormal = '" + receivedRates.getFullSubHours() +"' WHERE (parkingLotNumber = "+workerAssociation+");");
					System.out.println("Update done succesfully.");
				}
				stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+handledRequest.getType()+"', '"+handledRequest.getContent().toString()+"', '"+handledRequest.getReceiver()+"', '"+handledRequest.getSender()+"', '0');");
				myType.setSysMode(SystemMode.OK);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			}	
		return myType;
	}

	/**
	 * this method will get the current notification vector for the current user.
	 * @param sysType - the user to receive the notification vector.
	 * @return - the current notification vector for the user.
	 */
	
	public Vector<Notification> GetMessages(SystemTypes sysType){
		User currUser = (User)sysType.getObj();
		ResultSet rs = null;
		ResultSet rsSpec = null;
		Vector<Notification> unreadNotifications = new Vector<Notification>();
		float newocc = 0,newadv = 0, newnormhours = 0, newfullhours = 0,refund =0;
		String time=null,date=null;
		DateTime currDateTime = new DateTime();
		
		try { 
			if (currUser.getPrivilege()==User.NET_MANAGER){ // Get messages for NETWORK MANAGER.
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT * FROM notifications WHERE nType = 'RATES' AND nRead = 0 AND nReceiver = '"+currUser.getUserName()+"';" );
				while (rs.next())
				{
					RateSettingRequest newNotif = new RateSettingRequest();
					int nidForlater = rs.getInt("nid");
					newNotif.setNid(rs.getInt("nid"));
					newNotif.setType(rs.getString("nType"));
					newNotif.setSender(rs.getString("nSender"));
					newNotif.setReceiver(rs.getString("nReceiver"));
					newNotif.setContent(rs.getString("nContent"));
					newNotif.setRead(rs.getBoolean("nRead"));
					stmt = conn.createStatement();
					rsSpec = stmt.executeQuery("SELECT * FROM ratechanges WHERE nid = '"+nidForlater+"';");
					if (rsSpec.next())
					{
						newocc = rsSpec.getFloat("newoccrate");
						newadv = rsSpec.getFloat("newadvrate");
						newnormhours = rsSpec.getFloat("newnormhours");
						newfullhours = rsSpec.getFloat("newfullhours");
						System.out.println("NETWORK MANAGER new messages pulled succesfully.");
					}
					newNotif.setRatesToSet(newocc,newadv,newnormhours,newfullhours);
					unreadNotifications.addElement(newNotif);
				}
				return unreadNotifications;
			}
				
				else if (currUser.getPrivilege()==User.LOT_MANAGER){ // Get messages for LOT MANAGER.
					stmt = conn.createStatement();
					rs = stmt.executeQuery("SELECT * FROM notifications WHERE (nType = 'APPROVED_RATES' OR nType = 'DENIED_RATES') AND nRead = '0' AND nReceiver = '"+currUser.getUserName()+"';" );
					while (rs.next())
					{
						RateSettingRequest newNotif = new RateSettingRequest();
						int nidForlater = rs.getInt("nid");
						newNotif.setNid(rs.getInt("nid"));
						newNotif.setType(rs.getString("nType"));
						newNotif.setSender(rs.getString("nSender"));
						newNotif.setReceiver(rs.getString("nReceiver"));
						newNotif.setContent(rs.getString("nContent"));
						newNotif.setRead(rs.getBoolean("nRead"));
						unreadNotifications.addElement(newNotif);
						System.out.println("LOT MANAGER new messages pulled succesfully.");
					}
					return unreadNotifications;
				}
			
				else if (currUser.getPrivilege()==User.CLIENT){ // Get messages for Client.
					//====== Check non-replied complaints =======//
					stmt = conn.createStatement();
					rs = stmt.executeQuery("SELECT * FROM vcp.notifications WHERE (nType = 'COMPLAINT') AND nRead = '0' AND nSender = '"+currUser.getUserName()+"';" );
					while (rs.next())
					{
						int nid = rs.getInt("nid");
						rs = stmt.executeQuery("SELECT * FROM vcp.complaint WHERE nid = '"+nid+"' AND autoHandled = '0';");
						if (rs.next())
						{
							time = new String(rs.getString("complaintTime"));
							date = new String(rs.getString("complaintDate"));
							int daysDiff = Days.daysBetween(new DateTime(date), new DateTime(currDateTime)).getDays();
							if (daysDiff>0)
							{
								Notification complaintAutoResponse = new Notification();
								complaintAutoResponse.setReceiver(currUser.getUserName());
								SendAutoComplaintResponse(complaintAutoResponse); // Send auto-reply
								stmt.executeUpdate("UPDATE complaint SET autoHandled = '1' WHERE nid = '"+nid+"';"); // Mark auto-replied
							}
						}
						rs = stmt.executeQuery("SELECT * FROM vcp.subscribedclient WHERE clientNumber = '"+((Client)currUser).getClientNumber()+"'" +
								"AND autoHandled = '0';");
						if (rs.next())
						{
							date = new String(rs.getString("startDate"));
							int subNum = rs.getInt("subscriptionNumber");
							int daysDiff = Days.daysBetween(new DateTime(date), new DateTime(currDateTime)).getDays();
							if (daysDiff>20)
							{
								Notification subAutoResponse = new Notification();
								subAutoResponse.setReceiver(currUser.getUserName());
								SendSubscriptionReminder(subAutoResponse); // Send auto-reminder
								stmt.executeUpdate("UPDATE subscribedclient SET autoHandled = '1' WHERE subscriptionNumber = '"+subNum+"';"); // Mark auto-handled
							}
						}
					}
					//============================================//
					stmt = conn.createStatement();		
					rs = stmt.executeQuery("SELECT * FROM notifications WHERE (nType = 'COMPLAINT_RESPONSE' OR nType = 'LATE' OR nType = 'SUB_REMINDER') AND nRead = '0' AND nReceiver = '"+currUser.getUserName()+"';" );
					while (rs.next())
					{
						RateSettingRequest newNotif = new RateSettingRequest();
						int nidForlater = rs.getInt("nid");
						newNotif.setNid(rs.getInt("nid"));
						newNotif.setType(rs.getString("nType"));
						newNotif.setSender(rs.getString("nSender"));
						newNotif.setReceiver(rs.getString("nReceiver"));
						newNotif.setContent(rs.getString("nContent"));
						newNotif.setRead(rs.getBoolean("nRead"));
						unreadNotifications.addElement(newNotif);
						System.out.println("CLIENT new messages pulled succesfully.");
					}
					return unreadNotifications;
				}
				
				else if (currUser.getPrivilege()==User.CUST_SER_WORKER){ // Get messages for CS WORKER.
					stmt = conn.createStatement();
					rs = stmt.executeQuery("SELECT * FROM notifications WHERE nType = 'COMPLAINT' AND nRead = '0' AND nReceiver = 'Customer Service';" );
					while (rs.next())
					{
						ClientComplaint newComplaint = new ClientComplaint();
						int nidForlater = rs.getInt("nid");
						newComplaint.setNid(rs.getInt("nid"));
						newComplaint.setType(rs.getString("nType"));
						newComplaint.setSender(rs.getString("nSender"));
						newComplaint.setReceiver(rs.getString("nReceiver"));
						newComplaint.setContent(rs.getString("nContent"));
						newComplaint.setRead(rs.getBoolean("nRead"));
						stmt = conn.createStatement(); 
						rsSpec = stmt.executeQuery("SELECT * FROM complaint WHERE nid = '"+nidForlater+"';");
						if (rsSpec.next())
						{
							time = new String(rsSpec.getString("complaintTime"));
							date = new String(rsSpec.getString("complaintDate"));
							
						}		
						System.out.println("CS WORKER new messages pulled succesfully.");
						unreadNotifications.addElement(newComplaint);
					}
					return unreadNotifications;
				}
			}
		catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return null;
	}

	/**
	 * this method will send a complaint to Customer Service.
	 * @param sysType - the client complaint notification.
	 * @return - an approval message that the complaint was sent.
	 */
	
	public SystemTypes HandleComplaint(SystemTypes myType) {
		ResultSet rs;
		ClientComplaint receivedNotification = (ClientComplaint)myType.getObj();
		receivedNotification.setReceiver("Customer Service");
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			if (receivedNotification.getType().equals("COMPLAINT"))
			{
				stmt = conn.createStatement();
				rs = stmt.executeQuery("SELECT * FROM complaint;");
				while (rs.next())
					lastcid = rs.getInt("complaintNumber");
				try
				{
						stmt = conn.createStatement();
						stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+receivedNotification.getType()+"', '"+receivedNotification.getContent().toString()+"', '"+receivedNotification.getSender()+"', '"+receivedNotification.getReceiver()+"', '0');");
						stmt.executeUpdate("INSERT INTO `vcp`.`complaint` (`complaintNumber`, `nid`, `complaintTime`, `complaintDate`) VALUES ('"+(++lastcid)+"', '"+lastnid+"', '"+receivedNotification.getComplaintTime()+"', '"+receivedNotification.getComplaintDate()+"');");
						myType.setSysMode(SystemMode.OK);
						System.out.println("new complaint SENT succesfuly.");
				}
				catch (SQLException e) {
					e.printStackTrace();
					myType.setSysMode(SystemMode.DB_CON_ERR);
				}
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}
	
	/**
	 * this method will send a reply to the client's complaint notification.
	 * @param sysType - an object containing the reply message and amount of refund.
	 * @return - an approval message that the reply was sent.
	 */
	
	public SystemTypes HandleComplaintReply(SystemTypes myType) {
		ResultSet rs;
		Object[] obj = (Object[]) myType.getObj();
		Notification reply = (Notification)obj[0];
		float refund = (float) obj[1];
		reply.setType("COMPLAINT_RESPONSE");
		reply.setSender("Customer Service");
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+reply.getType()+"', '"+reply.getContent().toString()+"', '"+reply.getSender()+"', '"+reply.getReceiver()+"', '0');");
			UpdateBalance(reply,refund);
			myType.setSysMode(SystemMode.OK);
			System.out.println("Reply sent succesfully.");
		} catch (SQLException e) {
			myType.setSysMode(SystemMode.DB_CON_ERR);
			e.printStackTrace();
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}

	/**
	 * this method will update the client's balance.
	 * @param notification - the notification that contains the relevant user data.
	 * @param amount - the amount of money to change the client balance.
	 * @return - nothing.
	 */
	
	public void UpdateBalance(Notification notification, float amount) {
		ResultSet rs;
		String receiver = new String(notification.getReceiver());
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE client SET clientBalance = clientBalance+("+amount+") WHERE UserName = '"+receiver+"';");
			System.out.println("Balance updated succesfully.");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
	}
	
	/**
	 * this method will update client balance by his number
	 * @param client - the client number 
	 * @param amount - the amount to update
	 */
	
	public void UpdateBalanceByClient(int client, float amount) {
		ResultSet rs;
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE vcp.client SET clientBalance = clientBalance+("+amount+") WHERE clientNumber = '"+client+"';");
			System.out.println("Balance updated succesfully.");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
	}
	
	/**
	 * this method will send an auto-response to the client complaint after 24 hours.
	 * @param complaint - the notification that contains the relevant user data.
	 * @return - nothing.
	 */
	
	public void SendAutoComplaintResponse(Notification complaint){
		ResultSet rs;
		
		complaint.setSender("Customer Service");
		complaint.setType("COMPLAINT_RESPONSE");
		complaint.setContent(new String("Hello "+complaint.getReceiver()+",\nWe are currently unable to handle your complaint.\nA representative will provide you an answer soon.\n\nSincerely,\nVCP Auto Response Service"));
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+complaint.getType()+"', '"+complaint.getContent().toString()+"', '"+complaint.getSender()+"', '"+complaint.getReceiver()+"', '0');");
			System.out.println("Auto Response sent succesfully.");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	/**
	 * this method will calculate daily statistical information.
	 * @return - nothing.
	 */
	
	public void CalculateStatisticalInformation(){
		ResultSet rs;
		int ordersClosed=0,ordersCancelled=0,subTotal=0,subMultiple=0,ordersLate=0;
		Date date = new Date();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		String currDate = thedate.format(date);
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm");
		String currTime = thetime.format(date);
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM statistics;");
			while (rs.next())
				lastsid = rs.getInt("sid");
			for (int i=1;i<7;i++){
				rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE orderStatus = 'CLOSED' AND orderDate = '"+currDate+"';");
				if (rs.next())
					ordersClosed = rs.getFetchSize();
				rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE orderStatus = 'CANCELLED' AND orderDate = '"+currDate+"';");
				if (rs.next())
					ordersCancelled = rs.getFetchSize();
				rs = stmt.executeQuery("SELECT * FROM vcp.subscribedclient WHERE subscriptionStatus = 'VALID';");
				if (rs.next())
					subTotal = rs.getFetchSize();
				rs = stmt.executeQuery("SELECT * FROM vcp.subscribedclient,client WHERE subscribedclient.clientNumber = client.clientNumber AND client.numberOfVehicles > 1;");
				if (rs.next())
					subMultiple = rs.getFetchSize();
				rs = stmt.executeQuery("SELECT * FROM vcp.reminder WHERE reminderDate = '"+currDate+"';");
				if (rs.next())
					ordersLate = rs.getFetchSize();
				stmt.executeUpdate("INSERT INTO `vcp`.`statistics` (`sid`, `lotNumber`, `ordersClosed`, `ordersCancelled`, `subTotal`, `subMultiple`, `ordersLate`) VALUES ('"+(lastsid+1)+"', '"+i+"', '"+ordersClosed+"', '"+ordersCancelled+"', '"+subTotal+"', '"+subMultiple+"', '"+ordersLate+"');");
			}
			System.out.println("Statistical Data pulled succesfully.");
		}
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	/**
	 * this method will calculate weekly statistical information.
	 * @return - nothing.
	 */
	
	public void CalculateWeeklyData(){ // INCOMPLETE
		ResultSet rs;
		int ordersClosed[] = new int[7],
				ordersCancelled[] = new int[7],
				subTotal[] = new  int[7], 
				subMultiple[] = new int[7],
				ordersLate[] = new int[7];
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.weeklydata;");
			while (rs.next())
				lastwid = rs.getInt("wid");
			rs = stmt.executeQuery("SELECT * FROM vcp.statistics;");
			while (rs.next())
				lastsid = rs.getInt("sid");
			for (int j=1;j<7;j++){
				int sumOrdersClosed=0,sumOrdersCancelled=0,sumSubTotal=0,sumSubMultiple=0,sumOrdersLate=0;
				float medOrdersClosed=0,medOrdersCancelled=0,medSubTotal=0,medSubMultiple=0,medOrdersLate=0;
				for (int i=lastsid-7;i<lastsid;i++){
					rs = stmt.executeQuery("SELECT * FROM statistics WHERE sid = '"+(i+1)+"' AND lotNumber = '"+j+"';");
					if (rs.next()){
						sumOrdersClosed+=rs.getInt("ordersClosed");
						ordersClosed[i] = rs.getInt("ordersClosed");
						sumOrdersCancelled+=rs.getInt("ordersCancelled");
						ordersCancelled[i] = rs.getInt("ordersCancelled");
						sumSubTotal+=rs.getInt("subTotal");
						subTotal[i] = rs.getInt("subTotal");
						sumSubMultiple+=rs.getInt("subMultiple");
						subMultiple[i] = rs.getInt("subMultiple");
						sumOrdersLate+=rs.getInt("ordersLate");
						ordersLate[i] = rs.getInt("ordersLate");
					}
					Arrays.sort(ordersClosed);
					Arrays.sort(ordersCancelled);
					Arrays.sort(subTotal);
					Arrays.sort(subMultiple);
					Arrays.sort(ordersLate);
					medOrdersClosed = ordersClosed[4];
					medOrdersCancelled = ordersCancelled[4];
					medSubTotal = subTotal[4];
					medSubMultiple = subMultiple[4];
					medOrdersLate = ordersLate[4];
				}
				float avgOrdersClosed = sumOrdersClosed/7;
				float avgOrdersCancelled = sumOrdersCancelled/7;
				float avgSubTotal = sumSubTotal/7;
				float avgSubMultiple = sumSubMultiple/7;
				float avgOrdersLate = sumOrdersLate/7;
				stmt.executeUpdate("INSERT INTO `vcp`.`weeklydata` (`wid`, `lotNumber`, `avgClosed`, `avgCancelled`, `avgSubTotal`, `avgSubMultiple`, `avgLate`, `medClosed`, `medCancelled`, `medSubTotal`, `medSubMultiple`, `medLate`) VALUES " +
						"('"+(lastwid+1)+"', '"+j+"', '"+avgOrdersClosed+"', '"+avgOrdersCancelled+"', '"+avgSubTotal+"', '"+avgSubMultiple+"', '"+avgOrdersLate+
						"', '"+medOrdersClosed+"', '"+medOrdersCancelled+"', '"+medSubTotal+"', '"+medSubMultiple+"', '"+medOrdersLate+"');");
				}
			System.out.println("Statistical data updated succesfully.");
		}
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	/**
	 * this method will mark a notification as read by the user.
	 * @param sysType - the notification to be marked as read.
	 * @return - an approval message that the notification was marked as read.
	 */
	
	public SystemTypes MarkAsRead(SystemTypes myType) {
		ResultSet rs;
		
		try {
			stmt = conn.createStatement();
			Notification notification = (Notification)myType.getObj();
			stmt.executeUpdate("UPDATE notifications SET nRead = '1' WHERE (nid = '"+notification.getNid()+"');");
			System.out.println("Marked as Read succesfully.");
			myType.setSysMode(SystemMode.OK);	
		} 
		catch (SQLException e) {
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}
	
	/**
	 * this method will generate a performance report for the network manager.
	 * @param sysType - an object containing the report generation request data.
	 * @return - the report content.
	 */
	
	public SystemTypes GeneratePerformanceReport(SystemTypes myType) {
		ResultSet rs,rsSpec;
		int numOfMonthlySubcriptions = 0;
		int numOfSubscribedWithMultipleVehicles = 0;
		Date date = new Date();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		String currDate = thedate.format(date);
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm");
		String currTime = thetime.format(date);
		String reportContent = new String();
		
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM reports;");
			while (rs.next())
				lastrepid = rs.getInt("rid");
			rs = stmt.executeQuery("SELECT * FROM subscribedclient;");
			while (rs.next())
			{
				DateTime subDate = new DateTime(rs.getDate("startDate"));
				if (Days.daysBetween(DateTime.parse(currDate), subDate).getDays()<28){
					numOfMonthlySubcriptions++;
					rsSpec = stmt.executeQuery("SELECT * FROM subscribedclient,client WHERE subscribedclient.clientNumber = client.clientNumber AND client.numberOfVehicles > 1;");
					numOfSubscribedWithMultipleVehicles = rsSpec.getFetchSize();
				}
			}
			reportContent = "Performance Report for "+currDate.toString()+", "+currTime.toString()+" :\n\nNumber of Monthly Subscriptions : "+numOfMonthlySubcriptions+
					"\nNumber of Subscribed Clients with Multiple Vehicles : "+numOfSubscribedWithMultipleVehicles;
			stmt.executeUpdate("INSERT INTO `vcp`.`reports` (`rid`, `rtype`, `rcontent`, `rdate`, `rtime`) VALUES ('"+(++lastrepid)+"', 'PERFORMANCE', '"+reportContent+"', '"+currDate+"', '"+currTime+"');");
			myType.setObj(reportContent);
			myType.setSysMode(SystemMode.OK);
			System.out.println("Report issued succesfully.");
		} 
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	/**
	 * this method will generate an operation exception report for the network manager.
	 * @param sysType - an object containing the report generation request data.
	 * @return - the report content.
	 */
	
	public SystemTypes GenerateOperationExceptionReport(SystemTypes myType) { //INCOMPLETE
		ResultSet rs;
		String reportContent = new String("Operation Exception Report for the Last Week :\n\n");
		Date date = new Date();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		String currDate = thedate.format(date);
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm");
		String currTime = thetime.format(date);
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM weeklydata;");
			while (rs.next())
				lastwid = rs.getInt("wid");
			for (int j=1;j<7;j++){
				reportContent+="Lot No."+j+" : \n";
				rs = stmt.executeQuery("SELECT * FROM weeklydata WHERE wid = '"+lastwid+"' AND lotNumber = '"+j+"';");
				if (rs.next()){
					float avgOrdersCancelled = rs.getFloat("avgCancelled");
					float medOrdersCancelled = rs.getFloat("medCancelled");
					float avgOrdersLate = rs.getFloat("avgLate");
					float medOrdersLate = rs.getFloat("medLate");
					reportContent+="Cancelled orders average : "+avgOrdersCancelled+"\nCancelled orders median : "+medOrdersCancelled+
							"\nLate orders average : "+avgOrdersLate+"\nLate orders median : "+medOrdersLate+"\n\n";
				}
			}
			stmt.executeUpdate("INSERT INTO `vcp`.`reports` (`rid`, `rtype`, `rcontent`, `rdate`, `rtime`) VALUES ('"+(++lastrepid)+"', 'EXCEPTION', '"+reportContent+"', '"+currDate+"', '"+currTime+"');");
			myType.setObj(reportContent);
			myType.setSysMode(SystemMode.OK);
			System.out.println("Report issued succesfully.");
		}
		catch (SQLException e)
		{
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	
	public SystemTypes GenerateQuaterReport(SystemTypes myType) { //INCOMPLETE
		ResultSet rs;
		Date date = new Date();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		String currDate = thedate.format(date);
		DateTime curr = new DateTime(date);
		Date quarter = curr.minusMonths(4).toDate();
		String quarterStart = thedate.format(quarter);
		String reportContent;
		int occ = 0, adv = 0, norm = 0, full = 0, total = 0, closed = 0, susp = 0;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM reports;");
			while (rs.next())
				lastrepid = rs.getInt("rid");
			rs = stmt.executeQuery("SELECT * FROM order WHERE orderDate > '"+quarterStart+"';");
			while (rs.next())
			{
				String typeOfOrder = rs.getString("orderType");
				if (typeOfOrder.equals("OCCASIONAL")) occ++;
				if (typeOfOrder.equals("ADVANCE")) adv++;
				if (typeOfOrder.equals("NORMAL_SUB")) norm++;
				if (typeOfOrder.equals("FULL_SUB")) full++;
			}
			rs = stmt.executeQuery("SELECT * FROM notifications,complaint WHERE notifications.nid = complaint.nid AND complaint.complaintDate > '"+quarterStart+"';");
			while (rs.next())
			{
				total++;
				int read = rs.getInt("nRead");
				if (read==1) closed++;
			}
			rs = stmt.executeQuery("SELECT * FROM parkingLocations WHERE orderDate > '"+quarterStart+"';");
			while (rs.next())
			{
				int inactive = rs.getInt("hoursInactive");
				if (inactive>0) susp++;
			}
			Report quarterReport = new Report();
			//quarterReport.setContent(quarterReport.toQuarterReportMessage(lot.getParkingLotNumber(), occ, adv, norm, full, total, closed, susp));
			myType.setObj(quarterReport);
			myType.setSysMode(SystemMode.OK);
			System.out.println("Report issued succesfully.");
		}
		catch (SQLException e)
		{
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	/**
	 * this method will close record of order in the DB. 
	 * @param myType - the order to close in the DB.
	 * @return the result of the operation    
	 */
		
	public SystemTypes closeOrder(SystemTypes myType)
	{
		ResultSet rs1,rs2;
		Order order = (Order) myType.getObj();
		int oid = order.getOrderNumber();
		float rate=0,cost=0;
		int lot,client=0;
		Serializable[] obj = new Serializable[2];
		
		String status;
		Date date = new Date();
		DateTime jLeaveDate = new DateTime(date);
		SimpleDateFormat theDate = new SimpleDateFormat("dd/MM/yyyy");
		SimpleDateFormat theTime = new SimpleDateFormat("HH:mm:ss");
		DateTimeFormatter formatter = DateTimeFormat.forPattern("dd/MM/yyyy HH:mm:ss");
		DateTime orderDate,expectedLeave;
		try {
			stmt = conn.createStatement();
			rs1 = stmt.executeQuery("SELECT * FROM vcp.order WHERE orderNumber = "+oid+";");
			if (rs1.next())
			{
				lot = rs1.getInt("sidParkingLocation");
				String s = rs1.getString("orderStatus");
				String type = rs1.getString("orderType");
				if (s.equals("ACTIVE"))
				{
					client = rs1.getInt("clientNumber");
					Date orDate = rs1.getDate(2);
					String od = theDate.format(orDate);
					Time orTime = rs1.getTime(3);
					status = rs1.getString("orderStatus");
					Time arTime = rs1.getTime("expectedLeaveTime");
					rs2 = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE sid = "+lot+";");
					String ot = theTime.format(orTime);
					orderDate = formatter.parseDateTime(od + " " + ot);
					rs2.next();
					lot = rs2.getInt(2);
					rs2 =  stmt.executeQuery("SELECT * FROM vcp.parkinglot WHERE parkingLotNumber = "+lot+";");
					if (type.equals("OCCASIONAL"))
					{
						rs2.next();
						rate = rs2.getFloat(5);
						cost = calculateCasualCost(orderDate,jLeaveDate,rate,1); // CHANGED FOT TESTING - PART 1
						this.UpdateBalanceByClient(client, cost);
						System.out.println("The cost is:" + cost);
						//myType.setObj("Please pay "+ (-1*cost) + " to get your car back.");
						obj[0] = new String("Please pay "+ (-1*cost) + " to get your car back.");
						obj[1] =  String.valueOf(cost);
					}
					else
					{
						rs2.next();
						rate = rs2.getFloat(4);
						String et = theTime.format(arTime);
						expectedLeave = formatter.parseDateTime(od + " " +et);
						float diff = calculateFine(expectedLeave,jLeaveDate,cost); // CHANGED FOT TESTING - PART 2
						if (status.equals("LATE"))
							cost = calculateCasualCost(expectedLeave,jLeaveDate,rate,(float) (diff*1.2)); // CHANGED FOT TESTING - PART 1
						else
						{
							cost = calculateCasualCost(expectedLeave,jLeaveDate,rate,diff); // CHANGED FOT TESTING - PART 1
						}
						this.UpdateBalanceByClient(client, cost);
						if (cost<0){
							//myType.setObj("Please pay "+ (-1*cost) + " difference to get your car back.");
							obj[0] = new String("Please pay "+ (-1*cost) + " difference to get your car back.");
							obj[1] = String.valueOf(cost);
						}
						else{
							//myType.setObj("Please collect "+ (-1*cost) + " change and get your car back.");
							obj[0] = new String("Please collect "+ (-1*cost) + " change and get your car back.");
							obj[1] =  String.valueOf(cost);
						}
						}
					stmt.executeUpdate("UPDATE vcp.order SET orderStatus = 'CLOSED' WHERE orderNumber = "+oid+";");
					System.out.println("Order closed succesfully.");
					myType.setSysMode(SystemMode.OK);
				}
				else
				{
					myType.setSysMode(SystemMode.FAIL);
					//myType.setObj("Order wasn't closed - can't close inactive order.");
					obj[0] = new String("Order wasn't closed - can't close inactive order.");
					obj[1] = new String();
					System.out.println("Order wasn't closed - can't close inactive order.");
				}
			}
			else
			{
				myType.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("Order wasn't closed - incorrect order number.");
				obj[0] = new String("Order wasn't closed - incorrect order number.");
				obj[1] = new String();
			}
		} 
		catch (SQLException e) 
		{
			myType.setSysMode(SystemMode.DB_CON_ERR);
			obj[0] = new String("DB Connection error.");
			obj[1] = new String();
			e.printStackTrace();
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setObj(obj);
			return myType;
		}
		myType.setObj(obj);
		return myType;
	}
	
	// FOR TESTING - PART 1
	public float calculateCasualCost(DateTime orderDate, DateTime jLeaveDate, float rate, float diff){
		//System.out.println("enter calculateCasualCost");
		float cost = (float) ((Minutes.minutesBetween(orderDate,jLeaveDate).getMinutes()/60.0)*rate*-1*diff);
		DecimalFormat twoDForm = new DecimalFormat("#.##");
		return Float.valueOf(twoDForm.format(cost));
	}
	
	// FOR TESTING - PART 2
	public boolean checkIfClientIsLate(DateTime orderDate, DateTime arrivalDate){
		int diff = Minutes.minutesBetween(orderDate,arrivalDate).getMinutes();
		if (diff>0)
			return true;
		return false;
	}
	
	// FOR TESTING - PART 2
	public float calculateFine(DateTime orderDate, DateTime arrivalDate,float cost){
		float diff = Minutes.minutesBetween(orderDate, arrivalDate).getMinutes();
		if (diff<0)
			diff = (float) (1.2*diff*cost);

		return cost;
	}
		
	/**
	 * this method will create new record of order in the DB. 
	 * @param myType - the order to insert to the DB.
	 * @return if operation succeeded String for occasional and expected cost  for order in advance, Otherwise,
	 * the correct error message  
	 */
		
	public SystemTypes creatNewOrder(SystemTypes myType) {
		ResultSet rs;
		Order order=(Order)myType.getObj();
		int client = order.getClientNumber();
		int lot = order.getSidParkingLocation();
		String type = order.getOrderType();
		float exCost;
		int oid;
		try
		{
			if (!this.isLotFull(lot))
			{
				myType.setObj("The lot is Full, Go To parking lot worker to get alternative lot!");
				myType.setSysMode(SystemMode.FAIL);
				System.out.println("Order creation failed - The lot is Full.");
				return myType;
			}	
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE lotNumber = "+lot+";");
			if (!rs.first())
			{
				myType.setObj("The lot is not initialized, go To parking lot worker to handle the order!");
				myType.setSysMode(SystemMode.FAIL);
				System.out.println("Order creation failed - The lot is not initialized.");
				return myType;
			}
			rs = stmt.executeQuery("SELECT * FROM vcp.order;");
			if (rs.last())
				oid = rs.getInt(1)+1;
			else
				oid = 1;
			rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE clientNumber = '"+client+"' AND vehicleNumber = '"+order.getVehicleNumber()+"' AND orderStatus = 'PLANNED';");
		 	if (!type.equals("OCCASIONAL"))
				{
		 			if (rs.first()==false)
				 	{
		 				exCost = order.getExpectedCost();
			 			stmt.executeUpdate("INSERT INTO vcp.order (`orderNumber`, `orderDate`, `orderTime`, `orderType`, `expectedCost`,  `expectedLeaveTime`, `orderStatus`, `clientNumber`, `vehicleNumber`, `sidParkingLocation`,`eMail`,`leaveDate`) VALUES ('"+oid+"', '"+order.getOrderDate()+"', '"+order.getOrderTime()+"', '"+order.getOrderType()+"', '"+order.getExpectedCost()+"', '"+order.getExpectedLeaveTime()+"', 'PLANNED', '"+order.getClientNumber()+"', '"+order.getVehicleNumber()+"', '"+order.getSidParkingLocation()+"','"+order.geteMail()+"','"+order.getLeaveDate()+"');");
				 	}
		 			else
					{
						myType.setSysMode(SystemMode.EMPTY_RES);
						myType.setObj("Order creation failed - customer has open planned order.");
						System.out.println("Order creation failed - customer has open planned order.");
						return myType;
					}
		 			
				}
			else
			{
				if (CheckIfOrderExists(client)){
					myType.setObj("You already have an open occasional order !");
					myType.setSysMode(SystemMode.FAIL);
					System.out.println("Order creation failed - Open Order Exists.");
					return myType;
				}
				stmt.executeUpdate("INSERT INTO vcp.order (`orderNumber`, `orderDate`, `orderTime`, `arrivalTime`, `orderType`, `expectedLeaveTime`, `orderStatus`, `clientNumber`, `vehicleNumber`, `sidParkingLocation`,`eMail`,`leaveDate`) VALUES ('"+oid+"', '"+order.getOrderDate()+"', '"+order.getOrderTime()+"','"+order.getOrderTime()+"', '"+order.getOrderType()+"', '"+order.getExpectedLeaveTime()+":00', 'OPEN', "+order.getClientNumber()+", "+order.getVehicleNumber()+", "+order.getSidParkingLocation()+",'"+order.geteMail()+"','"+order.getLeaveDate()+"');");

		 	myType.setSysMode(SystemMode.OK);

			}
		 	System.out.println(7);
			myType.setSysMode(SystemMode.OK);

			System.out.println("Order created succesfully.");
			String msg = "Your order number: " + oid + "\nPlease keep it until leaving the lot.\n You will need it to get your car back.";
			myType.setObj(msg);
			if (!type.equals("OCCASIONAL"))
			{
			 	exCost = order.getExpectedCost();
				msg = msg + "\n" + exCost + " NIS Was reducted from your clients balance. \n The balance will be updated again when leaving the lot.";
				exCost = 0 - exCost;
				myType.setObj(msg);
				this.UpdateBalanceByClient(client, exCost);
				
			}
			return myType;
		}
		catch (SQLException e)
		{
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}	
	
	/**
	 * this method will send an auto message to a client who is late for his order.
	 * @param order - an object containing the relevant order data.
	 * @return - nothing.
	 */
	
	public void SendAutoReminder(Order order)
	{
		ResultSet rs;
		Reminder reminder = new Reminder();
		
		reminder.setSender("Auto Reminder");
		reminder.setType("REMINDER");
		reminder.setContent(new String("Hello client No."+order.getClientNumber()+",\nYou are late for order No."+order.getOrderNumber()+".\nPlease approve/deny your arrival.\n\nBest wishes,\nAuto Reminder"));
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM client,order WHERE client.clientNumber = order.clientNumber AND order.orderNumber = "+order.getOrderNumber()+";");
			if (rs.next())
			{
				reminder.setReceiver(rs.getString("UserName"));
			}
			rs = stmt.executeQuery("SELECT * FROM notifications;");
			while (rs.next())
				lastremid = rs.getInt("reminderNumber");
			stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+reminder.getType()+"', '"+reminder.getContent().toString()+"', '"+reminder.getSender()+"', '"+reminder.getReceiver()+"', '0');");
			stmt.executeUpdate("INSERT INTO `vcp`.`reminder` (`reminderNumber`, `orderNumber`, `reminderDate`, `reminderTime`, `reminderAnswer`) VALUES ('"+(++lastremid)+"', '"+order.getOrderNumber()+"', '"+reminder.getDate().toString()+"', '"+reminder.getTime().toString()+"', '', '0');");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	/**
	 * this method will send an auto subscription reminder to a client whose subscription is about to expire.
	 * @param order - an object containing the relevant order data.
	 * @return - nothing.
	 */
	
	public void SendSubscriptionReminder(Notification client)
	{
		ResultSet rs;
		Notification reminder = new Notification();
		
		reminder.setSender("Auto Reminder");
		reminder.setType("SUB_REMINDER");
		reminder.setContent(new String("Hello "+client.getReceiver()+",\nYour subscription is about to expire in 7 days.\nPlease renew your subscription.\n\nBest wishes,\nAuto Reminder"));
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.client,vcp.subscribedclient WHERE client.clientNumber = subscribedclient.clientNumber;");
			if (rs.next())
			{
				reminder.setReceiver(rs.getString("UserName"));
			}
			rs = stmt.executeQuery("SELECT * FROM vcp.notifications;");
			while (rs.next())
				lastnid = rs.getInt("nid");
			stmt.executeUpdate("INSERT INTO `vcp`.`notifications` (`nid`, `nType`, `nContent`, `nSender`, `nReceiver`, `nRead`) VALUES ('"+(++lastnid)+"', '"+reminder.getType()+"', '"+reminder.getContent().toString()+"', '"+reminder.getSender()+"', '"+reminder.getReceiver()+"', '0');");
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
	}
	
	/**
	 * this method will cancel order and will refund the client according to the refund policy.
	 * @param myType - the order to cancel
	 * @return status of the result of the request
	 */
	// FOR TESTING - PART 2
	public SystemTypes cancelOrder(SystemTypes myType) {
		ResultSet rs;
		Order cancel=(Order) myType.getObj();
		SimpleDateFormat theDate = new SimpleDateFormat("dd/MM/yyyy");
		SimpleDateFormat theTime = new SimpleDateFormat("HH:mm:ss");
		DateTimeFormatter formatter = DateTimeFormat.forPattern("dd/MM/yyyy HH:mm:ss");
		DateTime currDate = new DateTime();
		try
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE (clientNumber = '"+cancel.getClientNumber()+"' AND vehicleNumber = '"+cancel.getVehicleNumber()+"' AND orderStatus = 'PLANNED');");
			if (rs.first() == false)
			{
				myType.setSysMode(SystemMode.FAIL);
				System.out.println("Order canceling failed - Incorrect order number.");
				return myType;
			}
			int oid = rs.getInt(1);
			Date date = rs.getDate(2);
			String d = theDate.format(date);
			Time time = rs.getTime(3);
			String t = theTime.format(time);
			DateTime orderDate = formatter.parseDateTime(d + " " +t);
			float cost = rs.getFloat("expectedCost");
			/*int diff = Minutes.minutesBetween(currDate, orderDate).getMinutes();
			if (diff <180 && diff >=60 )
				cost = (float) (0.5*cost);
			else if (diff < 60)
				cost = 0;*/
			cost =cancelOrder( currDate , orderDate, cost);
			this.UpdateBalanceByClient(cancel.getClientNumber(), cost);
			stmt.executeUpdate("UPDATE vcp.order SET orderStatus = 'CANCELED' WHERE orderNumber = '"+oid+"';");
			
			myType.setObj("Refund in the sum of "+cost+" added to your balance.");
			System.out.println("Order cancelled succesfully.");
			myType.setSysMode(SystemMode.OK);
		} 
		catch (SQLException e)
		{
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}
	public float cancelOrder(DateTime currDate ,DateTime orderDate,float cost) {
			int diff = Minutes.minutesBetween(currDate, orderDate).getMinutes();
			if (diff <180 && diff >=60 )
				cost = (float) (0.5*cost);
			else if (diff < 60)
				cost = 0;
			return cost;

	}
	/**
	 * this method will check for the status of last order made given client name and optionally by car number
	 * @param myType - order object filled with client number and optionally vehicle number
	 * @return - status of the result of the request
	 */

	public SystemTypes trackOrder(SystemTypes myType) {
		ResultSet rs;
		Order order=(Order) myType.getObj();
		int numberOfVehicles;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.client WHERE clientNumber = "+order.getClientNumber()+";");
			if (rs.first() == false)
			{
				myType.setSysMode(SystemMode.FAIL);
				myType.setObj("Invalid client number!");
				System.out.println("Order track failed - Invalid client number.");
				return myType;
			}
			else
			{
				numberOfVehicles = rs.getInt("numberOfVehicles");
			}
			if (numberOfVehicles == 1)
				rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE (clientNumber = "+order.getClientNumber()+");");
			else
			{
				if (order.getVehicleNumber() != -1)
					rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE (clientNumber = "+order.getClientNumber()+" AND vehicleNumber = "+order.getVehicleNumber()+");");
				else
				{
					myType.setSysMode(SystemMode.FAIL);
					myType.setObj("Vehicle number necessary for this client number!");
					System.out.println("Order track failed - Vehicle number necessary for this client number!");
					return myType;
				}
			}
			if(rs.last() == true)
			{
				String status = rs.getString("orderStatus");
				myType.setSysMode(SystemMode.OK);
				myType.setObj("Your last request status is:" + status);
				System.out.println("Order status fetching done succesfully.");
				return myType;
			}
			else
			{
				myType.setSysMode(SystemMode.FAIL);
				myType.setObj("There are no orders for this client!");
				System.out.println("Order track failed - There are no orders for this client!");
				return myType;
			}
		} 
		catch (SQLException e)
		{
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}

	/**
	 * An assistance method to check whether lot is full using it's number
	 * @param lotNum - the number of the lot
	 * @return true if not full, otherwise false
	 */
	
	public boolean isLotFull(int lotNum)
	{
		ResultSet rs;
		
		try
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE parkingSpaceStatus = 'FREE' AND lotnumber = "+lotNum+";");
			if (rs.first())
				return true;
		}
		catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return false;
		
	}

	/**
	 * this method will get the rates for lot giving it's number
	 * @param myType - the lot to get rates for
	 * @return - the rates or the proper error message
	 */
	
	public SystemTypes getRatesForLot(SystemTypes myType)
	{
		ResultSet rs;
		ParkingLot lot = (ParkingLot) myType.getObj();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM parkinglot WHERE parkingLotNumber = "+lot.getParkingLotNumber()+";");
			if (rs.next())
			{
				lot.setAdvanceRate(rs.getFloat(4));
				lot.setOccasionalRate(rs.getFloat(5));
				lot.setHoursForNormal(rs.getFloat(6));
				lot.setHoursForRoutineS(rs.getFloat(7));
				lot.setHoursForRoutineM(rs.getFloat(8));
				myType.setObj(lot);
				System.out.println("Operation done succesfully!");
				myType.setSysMode(SystemMode.OK);
			}
			else
			{
				myType.setSysMode(SystemMode.EMPTY_RES);
				System.out.println("Operation falied - Incorrect parking lot number!");
			}
		} 
		catch (SQLException e) 
		{
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}

	/**
	 * this method will change the order status giving the answer for the reminder from the client
	 * @param myType - the order to change
	 * @return - the result of the operation
	 */
	
	public SystemTypes HandleReminderReply(SystemTypes myType) {
		ResultSet rs;
		int orderNumber = ((Reminder) myType.getObj()).getOrderNumber();
		String orderStatus = ((Reminder) myType.getObj()).getType();
		
		try {
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE order SET orderStatus = '"+orderStatus+"' WHERE (orderNumber = '"+orderNumber+"');");
			System.out.println("Operation done succesfully!");
			myType.setSysMode(SystemMode.OK);
		}
		catch (SQLException e) 
		{
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		return myType;
	}

	/**
	 * this method will create new subscription and will update the client's balance
	 * @param myType - the subscription to create
	 * @return - the result of the operation
	 */
	
	public SystemTypes createNewSubscription(SystemTypes myType)
	{
		SubscribedClient client = (SubscribedClient) myType.getObj();
		int clientNum = client.getClientNumber();
		ResultSet rs;
		String type = client.getTypeOfSubscription();
		int lot = client.getLotNumber();
		int subNum = 1;
		float cost = 0;
		try
		{
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM parkinglot WHERE parkingLotNumber = "+lot+";");
			if (rs.next())
			{
				float rate = rs.getFloat(4);
				float hoursS = rs.getFloat(7);
				float hoursM = rs.getFloat(8);
				if (!type.equals("NORMAL"))
						cost = fullSubCost(rate);
				else
				{
					stmt = conn.createStatement();
					rs = stmt.executeQuery("SELECT * FROM client WHERE clientNumber = "+clientNum+";");
					if (!rs.first())
					{
						myType.setObj("Invalid client number");
						myType.setSysMode(SystemMode.FAIL);
						System.out.println("Operation Failed - Invalid client number!");
						return myType;
					}
					int num = rs.getInt(3);
					if (num>1)
						cost = (float) (-0.9*rate*hoursM);
					else
						cost = (float) (-1*rate*hoursS);
					}
			}
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM subscribedclient;");
			if (rs.last())
				subNum = rs.getInt(1) + 1;
			stmt.executeUpdate("INSERT INTO subscribedclient (`subscriptionNumber`,`startDate`,`clientLeaveTime`,`typeOfSubscription`,`clientNumber`,`vehicleNumber`,`lotNumber`,`subscriptionStatus`,`clientLaveDate`,`sidParkingLocation` ) VALUES ('"+subNum+"','"+client.getStartDate()+"','"+client.getClientLeaveTime()+"','"+type+"','"+clientNum+"','"+client.getVehicleNumber()+"','"+lot+"','VALID','"+client.getClientLeaveDate()+"','"+client.getSidParkingLoaction()+"');");
			this.UpdateBalanceByClient(clientNum, cost);
			String result = "Your new suscription is now valid.\nThe subscription number is: " + subNum + "\n" + (-1*cost) + " reducted from your account.\n Please Pay the amount before entering the car.";
			myType.setObj(result);
			myType.setSysMode(SystemMode.OK);
			System.out.println("Operation done succesfully!");
		}
		catch (SQLException e) 
		{
			myType.setSysMode(SystemMode.DB_CON_ERR);
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return myType;
	}
	
	public float fullSubCost(float rate)
	{
		float cost;
		cost=-1*rate*72;
	return cost;
	}
	// Getters and setters for the class variables ********************************************************************************

	/**
	 * @return the conn
	 */
	public Connection getConn() {
		return conn;
	}

	/**
	 * @param conn the conn to set
	 */
	public void setConn(Connection conn) {
		this.conn = conn;
	}

	/**
	 * @return the password
	 */
	public String getPassword() {
		return password;
	}

	/**
	 * @param password the password to set
	 */
	public void setPassword(String password) {
		this.password = password;
	}

	/**
	 * @return the host
	 */
	public String getHost() {
		return host;
	}

	/**
	 * @return the lib
	 */
	public String getLib() {
		return lib;
	}

	/**
	 * @return the user
	 */
	public String getUser() {
		return user;
	}

	
	/**
	 * this method will renew an existing subscription and will update the client's balance
	 * @param myType - the subscription to renew
	 * @return - the result of the operation
	 */
	public SystemTypes renewSubscription(SystemTypes myType) { // NOT TESTED
		ResultSet rs;
		Date date = new Date();
		SimpleDateFormat thedate = new SimpleDateFormat("yyyy-MM-dd");
		String currDate = thedate.format(date);
		SubscribedClient sub = (SubscribedClient)myType.getObj();
		int clientNumber = sub.getClientNumber();
		int subNumber = sub.getSubscriptionNumber();
		int lotNumber = 0;
		float balance = 0;
		float cost = 0;
		int vehicles = 0;
		String type = "";
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM client WHERE clientNumber = '"+clientNumber+"';");
			if (!rs.first()){
				String message = new String("Client not found !");
				myType.setObj(message);
				myType.setSysMode(SystemMode.EMPTY_RES);
				return myType;
			}
			else{
				balance = rs.getFloat("clientBalance");
				vehicles = rs.getInt("numberOfVehicles");
			}
			rs = stmt.executeQuery("SELECT * FROM subscribedclient WHERE subscriptionNumber = '"+subNumber+"';");
			if (!rs.first()){
				String message = new String("Subscription not found !");
				myType.setObj(message);
				myType.setSysMode(SystemMode.EMPTY_RES);
				return myType;
			}
			else{
				type = rs.getString("typeOfSubscription");
				lotNumber = rs.getInt("lotNumber");
				rs = stmt.executeQuery("SELECT * FROM parkingLot WHERE parkingLotNumber = '"+lotNumber+"';");
				if (rs.next())
				{
					if (type.equals("NORMAL")){
						if (vehicles<=1){
							cost = rs.getFloat("advanceRate") * rs.getFloat("hoursForRoutineS");
						}
						else
							cost = rs.getFloat("advanceRate") * rs.getFloat("hoursForRoutineM") * vehicles * (9/10);
					}
					else if (type.equals("FULL")){
						cost = rs.getFloat("advanceRate") * rs.getFloat("hoursForNormal");
					}
					if (cost > balance){
						String message = new String("Client balance too low !");
						myType.setObj(message);
						myType.setSysMode(SystemMode.FAIL);
						return myType;
					}
					stmt.executeUpdate("UPDATE subscribedclient SET startDate = '"+currDate+"' WHERE (subscriptionNumber = '"+subNumber+"');");
					UpdateBalanceByClient(clientNumber,-cost);
					String message = new String("Subscription renewed successfully !");
					myType.setObj(message);
					myType.setSysMode(SystemMode.OK);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	/**
	 * this method will get the new client balance after paying for parking
	 * @param myType - the client
	 * @return - the result of the operation
	 */
	public SystemTypes GetNewBalance(SystemTypes myType) {
		ResultSet rs;
		Client client = (Client)myType.getObj();
		
		UpdateBalanceByClient(client.getClientNumber(),client.getClientBalance());
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM client WHERE clientNumber = '"+client.getClientNumber()+"';");
			if (!rs.first()){
				myType.setSysMode(SystemMode.EMPTY_RES);
				return myType;
			}
			else{
				float balance = rs.getFloat("clientBalance");
				client.setClientBalance(balance);
				myType.setSysMode(SystemMode.OK);
				String message = new String("Thank you, "+client.getUserFirstName()+"\nYour new balance is : "+balance+"\nPlease come again !");
				myType.setObj(message);
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	/**
	 * this method will check if there are OPEN or PLANNED orders for a client
	 * @param myType - the client
	 * @return - the result of the operation
	 */
	private boolean CheckIfOrderExists(int clientNumber){
		ResultSet rs;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE clientNumber = '"+clientNumber+"' AND orderType = 'OCCASIONAL';");
			while (rs.next())
			{
				String status = rs.getString("orderStatus");
				if (status.equals("OPEN") || status.equals("PLANNED"))
					return true;
			}
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
} // End of DataBaseConnection
